package by.blog.dao.dao;

import by.blog.dao.beans.Category;
import by.blog.dao.dao.factory.MysqlDaoFactory;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Бондарчик Антон  15.11.2014.
 */
public class DaoCategory implements DaoInterface<Category, Integer> {

    /**
     * This fields are used for creating SQL query to table.
     * There are name of table and column names of this table.
     */
    private String tableName = "category";
    private String columnName1 = "category_id";
    private String columnName2 = "category_name";
    private String columnName3 = "parent_id";

    /**
     * This method insert (Category) category into table of DB.
     * @param category
     * @return true if the operation was successful, if else  - return false.
     */
    public  boolean  createUnit(Category category) {

        try {
            String create = "INSERT INTO " + tableName + " ( " +
                    columnName1 + ", " +
                    columnName2 + ", " +
                    columnName3 +
                    " ) VALUES (?, ?, ?)";

            PreparedStatement statement = MysqlDaoFactory.prepareStatement(create);

            statement.setInt(1, category.getCategory_id());
            statement.setString(2, category.getCategory_name());
            statement.setInt(3, category.getParent_id());

            statement.executeUpdate();
            return true;
        }
        catch (SQLException e) {

            return false;
        }
    }

    /**
     * This method returns film from DB by his index.
     * Method use load() to create object (Film) film
     * @param INDEX
     * @return (Film) film
     */
    public Category readUnit(Integer INDEX) {

        try {
            String read = "SELECT * FROM " + tableName + " WHERE " + columnName1 + "=?";
            PreparedStatement statement = MysqlDaoFactory.prepareStatement(read);

            statement.setInt(1, INDEX);

            ResultSet result = statement.executeQuery();

            Category category = null;

            while ((result).next()) {
                category = load(result);
            }
            return category;
        }
        catch (SQLException e) {

            return null;
        }
    }

    public Category readUnit(String CATEGORY_NAME) {

        try {
            String read = "SELECT * FROM " + tableName + " WHERE " + columnName2 + "=?";
            PreparedStatement statement = MysqlDaoFactory.prepareStatement(read);

            statement.setString(1, CATEGORY_NAME);

            ResultSet result = statement.executeQuery();

            Category category = null;

            while ((result).next()) {
                category = load(result);
            }
            return category;
        }
        catch (SQLException e) {

            return null;
        }
    }

    /**
     * This method create object (Film) film via ResultSet.
     * @param RS (ResultSet)
     * @return Film, created from ResultSet, or null if the operation wasn't successful.
     * @throws SQLException
     */
    private static Category load(ResultSet RS)  throws SQLException {
        Category category = new Category();
        try {
            category.setCategory_id(RS.getInt(1));
            category.setCategory_name(RS.getString(2));
            category.setParent_id(RS.getInt(3));
        }
        catch (SQLException e) {
            return null;
        }
        return category;
    }

    /**
     * This method update row in DB
     * @param category
     * @return true if the operation was successful, if else  - return false.
     */

    public  boolean updateUnit(Category category) {
        try {
            String update = "UPDATE " + tableName + " SET "
                    + columnName2 + " =?, "              //category_name
                    + columnName3 + " =? WHERE "         //parent_id
                    + columnName1 + "=?";

            PreparedStatement statement = MysqlDaoFactory.prepareStatement(update);

            statement.setString(1, category.getCategory_name());
            statement.setInt(2, category.getParent_id());
            statement.setInt(3, category.getCategory_id());

            statement.executeUpdate();

            return true;
        } catch (SQLException e) {

            return false;
        }
    }

    /**
     * This method delete category from DB by index.
     * @param INDEX
     * @return true if the operation was successful, if else  - return false.
     */
    public  boolean deleteUnit(Integer INDEX) {
        try {
            String del = "DELETE FROM " + tableName + " WHERE " + columnName1 + "=?";
            PreparedStatement statement = MysqlDaoFactory.prepareStatement(del);

            statement.setInt(1, INDEX);
            statement.executeUpdate();

            return true;
        }
        catch (SQLException e) {

            return false;
        }
    }

    /**
     * This method select objects by PARENT_ID and returns them in an ArrayList
     * @param PARENT_ID
     * @return ArrayList with objects, which have a PARENT_ID that we need
     */
    public List<Category> readUnitsByParent(int PARENT_ID) {
        try {
            String read = "SELECT * FROM " + tableName + " WHERE " + columnName3 + " =?";

            PreparedStatement statement = MysqlDaoFactory.prepareStatement(read);
            statement.setInt(1, PARENT_ID);

            ResultSet result = statement.executeQuery();

            return loadUnits(result);
        }
        catch (SQLException e) {

            return null;
        }
    }

    /**
     * This method select all objects from table and returns them in an ArrayList
     * @return ArrayList with all objects from table
     */
    public List<Category> readAllUnits() {
        try {
            String read = "SELECT * FROM " + tableName;

            PreparedStatement statement = MysqlDaoFactory.prepareStatement(read);
            ResultSet result = statement.executeQuery();

            return loadUnits(result);
        }
        catch (SQLException e) {

            return null;
        }
    }

    /**
     * This method used to make readUnitsByParent() and readAllUnits() easier.
     * @param RS
     * @return ArrayList with objects from ResultSet
     */
    private static List<Category> loadUnits(ResultSet RS) {
        try {
            List<Category> categoryArrayList = new ArrayList<Category>();
            Category category = null;

            while ((RS).next()) {

                category = load(RS);
                categoryArrayList.add(category);
            }
            return categoryArrayList;
        }
        catch (SQLException e) {
            return null;
        }
    }
}
